﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;



    public class datSinhVien
    {
        SqlCommand cmd;
        SqlDataReader dr;
        public List<entSinhVien> checkUpdateLyLich(string masv)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienCheckUpdateLyLich", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("MaSinhVien", DbType.String);
            sqla[0].Value = masv;
            cmd.Parameters.AddRange(sqla);
            dr = cmd.ExecuteReader();
            entSinhVien entsv = new entSinhVien();
            List<entSinhVien> lst = new List<entSinhVien>();
            while (dr.Read())
            {
                entsv = new entSinhVien();
                entsv.MaSinhVien = dr["MaSinhVien"].ToString();
                lst.Add(entsv);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;
        }
        public List<entSinhVien> getSinhVienDangNhap(entSinhVien sv)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienDangNhap", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla=new SqlParameter[2];
            sqla[0] = new SqlParameter("MaSinhVien", DbType.String);
            sqla[1] = new SqlParameter("MatKhau", DbType.String);

            sqla[0].Value = sv.MaSinhVien;
            sqla[1].Value = sv.MatKhau;
            cmd.Parameters.AddRange(sqla);

            dr = cmd.ExecuteReader();
            entSinhVien entsv = new entSinhVien();
            List<entSinhVien> lst=new List<entSinhVien>();
            while (dr.Read())
            {
                entsv = new entSinhVien();
                entsv.MaSinhVien = dr["MaSinhVien"].ToString();
                entsv.Ten = dr["Hoten"].ToString();
                entsv.MaLop = dr["maLop"].ToString();
                lst.Add(entsv);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;
        }
        public entSinhVien getSinhVienByMaSV(String masv)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienGetByMaSV", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("masinhvien", DbType.String);
            sqla[0].Value = masv;

            cmd.Parameters.AddRange(sqla);

            dr = cmd.ExecuteReader();
            entSinhVien entSV = new entSinhVien();
            if (dr.Read())
            {
                entSV.MaSinhVien=dr["masinhvien"].ToString();
                entSV.Ten=dr["ten"].ToString();
                entSV.MaLop=dr["malop"].ToString();
                entSV.NgaySinh=dr["ngaysinh"].ToString();
                entSV.MatKhau = dr["MatKhau"].ToString();
                entSV.Ho = dr["ho"].ToString();
                entSV.CMTND = dr["cmtnd"].ToString();
                entSV.DiaChi = dr["diachi"].ToString();
                entSV.HoKhau = dr["hokhau"].ToString();
                if (dr["dantoc"].ToString() != "")
                {
                    entSV.DanToc = int.Parse(dr["dantoc"].ToString());
                }
                if (dr["gioitinh"].ToString() != "")
                {
                    entSV.GioiTinh = int.Parse(dr["gioitinh"].ToString());
                }
                entSV.HoTenBo = dr["hotenbo"].ToString();
                entSV.NNBo = dr["nnbo"].ToString();
                entSV.HoTenMe = dr["hotenme"].ToString();
                entSV.NNMe = dr["nnme"].ToString();
                entSV.NgayCap = dr["ngayCap"].ToString();
                entSV.TonGiao = dr["TonGiao"].ToString();
                entSV.NoiSinh = dr["noisinh"].ToString();
                entSV.NoiCap = dr["noicap"].ToString();
                entSV.HoTenBo = dr["hotenbo"].ToString();
                entSV.NNBo = dr["nnbo"].ToString();
                entSV.NNMe = dr["nnme"].ToString();
                entSV.HoTenMe = dr["hotenme"].ToString();
                entSV.NamTN12 = dr["namtn12"].ToString();
                entSV.SoHieuBang12 = dr["sohieubang12"].ToString();
                entSV.Email = dr["Email"].ToString();
                entSV.DiaChiHT = dr["diachiht"].ToString();
                entSV.DienThoai = dr["dienthoai"].ToString();
                entSV.Khoa = dr["makhoa"].ToString();
            }
            dr.Close();
            sqlConnection.Close();
            return entSV;

        }
        public List<entSinhVien> getSinhVienByMaLop(String malop)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienGetByMalop", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("malop", DbType.String);
            sqla[0].Value = malop;

            cmd.Parameters.AddRange(sqla);

            dr = cmd.ExecuteReader();
            entSinhVien entSV = new entSinhVien();
            List<entSinhVien> lst = new List<entSinhVien>();
            while (dr.Read())
            {
                entSV = new entSinhVien();
                entSV.MaSinhVien = dr["masinhvien"].ToString();
                entSV.Ten = dr["ten"].ToString();
                entSV.MaLop = dr["malop"].ToString();
                entSV.NgaySinh = dr["ngaysinh"].ToString();
                entSV.MatKhau = dr["MatKhau"].ToString();
                entSV.Ho = dr["ho"].ToString();
                entSV.CMTND = dr["cmtnd"].ToString();
                entSV.DiaChi = dr["diachi"].ToString();
                entSV.HoKhau = dr["hokhau"].ToString();
                if (dr["dantoc"].ToString() != "")
                {
                    entSV.DanToc = int.Parse(dr["dantoc"].ToString());
                }
                if (dr["gioitinh"].ToString() != "")
                {
                    entSV.GioiTinh = int.Parse(dr["gioitinh"].ToString());
                }
                entSV.HoTenBo = dr["hotenbo"].ToString();
                entSV.NNBo = dr["nnbo"].ToString();
                entSV.HoTenMe = dr["hotenme"].ToString();
                entSV.NNMe = dr["nnme"].ToString();
                entSV.NgayCap = dr["ngayCap"].ToString();
                entSV.TonGiao = dr["TonGiao"].ToString();
                entSV.NoiSinh = dr["noisinh"].ToString();
                entSV.NoiCap = dr["noicap"].ToString();
                entSV.HoTenBo = dr["hotenbo"].ToString();
                entSV.NNBo = dr["nnbo"].ToString();
                entSV.NNMe = dr["nnme"].ToString();
                entSV.HoTenMe = dr["hotenme"].ToString();
                entSV.NamTN12 = dr["namtn12"].ToString();
                entSV.SoHieuBang12 = dr["sohieubang12"].ToString();
                entSV.Email = dr["Email"].ToString();
                entSV.DiaChiHT = dr["diachiht"].ToString();
                entSV.DienThoai = dr["dienthoai"].ToString();
                entSV.Khoa = dr["makhoa"].ToString();
                lst.Add(entSV);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;

        }
        public List<entSinhVien> search(String malop, string masinhvien, string tensinhvien, int pageIndex, string pageSize)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienSearch", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[5];
            sqla[0] = new SqlParameter("malop", DbType.String);
            sqla[1] = new SqlParameter("masinhvien", DbType.String);
            sqla[2] = new SqlParameter("tensinhvien", DbType.String);
            sqla[3] = new SqlParameter("@PageIndex", DbType.Int32);
            sqla[4] = new SqlParameter("@PageSize", DbType.Int32);
            
            sqla[0].Value = malop;
            sqla[1].Value = masinhvien;
            sqla[2].Value = tensinhvien;
            sqla[3].Value = pageIndex;
            sqla[4].Value = pageSize;

            cmd.Parameters.AddRange(sqla);

            dr = cmd.ExecuteReader();
            entSinhVien entSV = new entSinhVien();
            List<entSinhVien> lst = new List<entSinhVien>();
            while (dr.Read())
            {
                entSV = new entSinhVien();
                entSV.MaSinhVien = dr["masinhvien"].ToString();
                entSV.Ten = dr["ten"].ToString();
                entSV.MaLop = dr["malop"].ToString();
                entSV.NgaySinh = dr["ngaysinh"].ToString();
                entSV.MatKhau = dr["MatKhau"].ToString();
                entSV.Ho = dr["ho"].ToString();
                entSV.CMTND = dr["cmtnd"].ToString();
                entSV.DiaChi = dr["diachi"].ToString();
                entSV.HoKhau = dr["hokhau"].ToString();
                if (dr["dantoc"].ToString() != "")
                {
                    entSV.DanToc = int.Parse(dr["dantoc"].ToString());
                }
                if (dr["gioitinh"].ToString() != "")
                {
                    entSV.GioiTinh = int.Parse(dr["gioitinh"].ToString());
                }
                entSV.HoTenBo = dr["hotenbo"].ToString();
                entSV.NNBo = dr["nnbo"].ToString();
                entSV.HoTenMe = dr["hotenme"].ToString();
                entSV.NNMe = dr["nnme"].ToString();
                entSV.NgayCap = dr["ngayCap"].ToString();
                entSV.TonGiao = dr["TonGiao"].ToString();
                entSV.NoiSinh = dr["noisinh"].ToString();
                entSV.NoiCap = dr["noicap"].ToString();
                entSV.HoTenBo = dr["hotenbo"].ToString();
                entSV.NNBo = dr["nnbo"].ToString();
                entSV.NNMe = dr["nnme"].ToString();
                entSV.HoTenMe = dr["hotenme"].ToString();
                entSV.NamTN12 = dr["namtn12"].ToString();
                entSV.SoHieuBang12 = dr["sohieubang12"].ToString();
                entSV.Email = dr["Email"].ToString();
                entSV.DiaChiHT = dr["diachiht"].ToString();
                entSV.DienThoai = dr["dienthoai"].ToString();
                entSV.Khoa = dr["makhoa"].ToString();
                entSV.recordCount = dr["Record"].ToString();
                entSV.RowNumber = dr["RowNumber"].ToString();
                lst.Add(entSV);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;

        }
        public List<entSinhVien> getSinhVienAll()
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("select hokhau, malop, diachi, masinhvien, ho+' '+ten as ten, convert(varchar(10), ngaysinh,103) as ngaysinh,gioitinh from SinhVien where masinhvien='313402001'", sqlConnection);
            dr = cmd.ExecuteReader();
            entSinhVien entSV = new entSinhVien();
            List<entSinhVien> lst=new List<entSinhVien>();
            while (dr.Read())
            {
                entSV = new entSinhVien();
                entSV.MaSinhVien = dr["masinhvien"].ToString();
                entSV.Ten = dr["ten"].ToString();
                entSV.NgaySinh = dr["ngaysinh"].ToString();
                entSV.DiaChi = dr["diachi"].ToString();
                entSV.HoKhau = dr["hokhau"].ToString();
                entSV.MaLop = dr["malop"].ToString();
                if (dr["gioitinh"].ToString() != "")
                {
                    entSV.GioiTinh = int.Parse(dr["gioitinh"].ToString());
                }
                lst.Add(entSV);
            }
            dr.Close();
            sqlConnection.Close();
            return lst;

        }
        public void updatemk(entSinhVien entSV)

        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienUpdateMK", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[2];
            sqla[0] = new SqlParameter("masinhvien", DbType.String);
            sqla[1] = new SqlParameter("matkhau", DbType.String);
            sqla[0].Value = entSV.MaSinhVien;
            sqla[1].Value = entSV.MatKhau;
            cmd.Parameters.AddRange(sqla);
            cmd.ExecuteNonQuery();
            sqlConnection.Close();
    
        }
        public void resetmk(string msv)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienResetMK", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[1];
            sqla[0] = new SqlParameter("masinhvien", DbType.String);

            sqla[0].Value = msv;           
            cmd.Parameters.AddRange(sqla);
            cmd.ExecuteNonQuery();
            sqlConnection.Close();

        }
        public void updatealllylich(entSinhVien entSV)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienUpdateAllLyLich", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[23];
            sqla[0] = new SqlParameter("masinhvien", DbType.String);
            sqla[1] = new SqlParameter("Ho", DbType.String);
            sqla[2] = new SqlParameter("Ten", DbType.String);
            sqla[3] = new SqlParameter("NgaySinh", DbType.String);
            sqla[4] = new SqlParameter("Gioitinh", DbType.String);
            sqla[5] = new SqlParameter("CMTND", DbType.String);
            sqla[6] = new SqlParameter("HoKhau", DbType.String);
            sqla[7] = new SqlParameter("NoiSinh", DbType.String);
            sqla[8] = new SqlParameter("NamTotNghiep", DbType.String);
            sqla[9] = new SqlParameter("SoHieuBang12", DbType.String);
            sqla[10] = new SqlParameter("DanToc", DbType.Int16);
            sqla[11] = new SqlParameter("TonGiao", DbType.String);
            sqla[12] = new SqlParameter("NgayCap", DbType.String);
            sqla[13] = new SqlParameter("NoiCap", DbType.String);
            sqla[14] = new SqlParameter("hotenbo", DbType.String);
            sqla[15] = new SqlParameter("hotenme", DbType.String);
            sqla[16] = new SqlParameter("nnbo", DbType.String);
            sqla[17] = new SqlParameter("nnme", DbType.String);
            sqla[18] = new SqlParameter("email", DbType.String);
            sqla[19] = new SqlParameter("diachi", DbType.String);
            sqla[20] = new SqlParameter("diachiht", DbType.String);
            sqla[21] = new SqlParameter("ghichu", DbType.String);
            sqla[22] = new SqlParameter("dienthoai", DbType.String);
            

            sqla[0].Value = entSV.MaSinhVien;
            sqla[1].Value = entSV.Ho;
            sqla[2].Value = entSV.Ten;
            sqla[3].Value = entSV.NgaySinh;
            sqla[4].Value = entSV.GioiTinh;
            sqla[5].Value = entSV.CMTND;
            sqla[6].Value = entSV.HoKhau;
            sqla[7].Value = entSV.NoiSinh;
            sqla[8].Value = entSV.NamTN12;
            sqla[9].Value = entSV.SoHieuBang12;
            sqla[10].Value = entSV.DanToc;
            sqla[11].Value = entSV.TonGiao;
            sqla[12].Value = entSV.NgayCap;
            sqla[13].Value = entSV.NoiCap;
            sqla[14].Value = entSV.HoTenBo;
            sqla[15].Value = entSV.HoTenMe;
            sqla[16].Value = entSV.NNBo;
            sqla[17].Value = entSV.NNMe;
            sqla[18].Value = entSV.Email;
            sqla[19].Value = entSV.DiaChi;
            sqla[20].Value = entSV.DiaChiHT;
            sqla[21].Value = entSV.GhiChu;
            sqla[22].Value = entSV.DienThoai;
            cmd.Parameters.AddRange(sqla);

            cmd.ExecuteNonQuery();
            sqlConnection.Close();
        }

        public void updatelylich(entSinhVien entSV)
        {
            string constr = System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            SqlConnection sqlConnection = new SqlConnection(constr);
            sqlConnection.Open();
            cmd = new SqlCommand("sp_SinhVienUpdateLyLich", sqlConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter[] sqla = new SqlParameter[5];
            //sqla[0] = new SqlParameter("masinhvien", DbType.String);
            //sqla[1] = new SqlParameter("Ho", DbType.String);
            //sqla[2] = new SqlParameter("Ten", DbType.String);
            //sqla[3] = new SqlParameter("NgaySinh", DbType.String);
            //sqla[4] = new SqlParameter("Gioitinh", DbType.String);
            //sqla[5] = new SqlParameter("CMTND", DbType.String);
            //sqla[6] = new SqlParameter("HoKhau", DbType.String);
            //sqla[7] = new SqlParameter("NoiSinh", DbType.String);
            //sqla[8] = new SqlParameter("NamTotNghiep", DbType.String);
            //sqla[9] = new SqlParameter("SoHieuBang12", DbType.String);
            //sqla[10] = new SqlParameter("DanToc", DbType.String);
            //sqla[11] = new SqlParameter("TonGiao", DbType.String);
            //sqla[12] = new SqlParameter("NgayCap", DbType.String);
            //sqla[13] = new SqlParameter("NoiCap", DbType.String);
            //sqla[14] = new SqlParameter("hotenbo", DbType.String);
            //sqla[15] = new SqlParameter("hotenme", DbType.String);
            //sqla[16] = new SqlParameter("nnbo", DbType.String);
            //sqla[17] = new SqlParameter("nnme", DbType.String);

            //sqla[0].Value = entSV.MaSinhVien;
            //sqla[1].Value = entSV.Ho;
            //sqla[2].Value = entSV.Ten;
            //sqla[3].Value = entSV.NgaySinh;
            //sqla[4].Value = entSV.GioiTinh;
            //sqla[5].Value = entSV.CMTND;
            //sqla[6].Value = entSV.HoKhau;
            //sqla[7].Value = entSV.NoiSinh;
            //sqla[8].Value = entSV.NamTN12;
            //sqla[9].Value = entSV.SoHieuBang12;
            //sqla[10].Value = entSV.DanToc;
            //sqla[11].Value = entSV.TonGiao;
            //sqla[12].Value = entSV.NgayCap;
            //sqla[13].Value = entSV.NoiCap;
            //sqla[14].Value = entSV.HoTenBo;
            //sqla[15].Value = entSV.HoTenMe;
            //sqla[16].Value = entSV.NNBo;
            //sqla[17].Value = entSV.NNMe;

            sqla[0] = new SqlParameter("masinhvien", DbType.String);
            sqla[1] = new SqlParameter("Diachiht", DbType.String);
            sqla[2] = new SqlParameter("email", DbType.String);
            sqla[3] = new SqlParameter("dienthoai", DbType.String);
            sqla[4] = new SqlParameter("Gioitinh", DbType.Int16);


            sqla[0].Value = entSV.MaSinhVien;
            sqla[1].Value = entSV.DiaChiHT;
            sqla[2].Value = entSV.Email;
            sqla[3].Value = entSV.DienThoai;
            sqla[4].Value = entSV.GioiTinh;
            cmd.Parameters.AddRange(sqla);

            cmd.ExecuteNonQuery();
            sqlConnection.Close();
        }
    }


